﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetRightListToClass]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetRightListToClass];
GO
CREATE PROCEDURE [dbo].[sproc_GetRightListToClass]
    @UserName nvarchar(50),
    @ClassID  int
AS
BEGIN
SET NOCOUNT ON

DECLARE @actid INT;
DECLARE @ReturnValue INT;
DECLARE @rightTable TABLE (act_id INT);

IF @ClassID > 0 
BEGIN

    DECLARE cur_action CURSOR FOR SELECT proc_id FROM dbo.uds_proc;

    OPEN cur_action;
    FETCH NEXT FROM cur_action INTO @actid;
    WHILE @@FETCH_STATUS=0
    BEGIN

        exec dbo.sproc_GetAccessPermission
            @Class_ID = @ClassID,
            @Username = @UserName, 
            @Act_ID = @actid,
            @ReturnValue = @ReturnValue output;
        IF @ReturnValue > 0
            INSERT INTO @rightTable VALUES( @actid );
    
        FETCH NEXT FROM cur_action INTO @actid;

    END

    -- close and deallocate the cursor.
    CLOSE cur_action;
    DEALLOCATE cur_action;

    SELECT * 
        FROM dbo.uds_proc
        WHERE
            proc_id IN (SELECT act_id FROM @rightTable) 
            and proc_id IN (
                    SELECT a.proc_id
                        FROM 
                            dbo.uds_proc_type a,
                            dbo.uds_class b 
                        WHERE
                            a.proc_type = b.classtype 
                            and b.classid = @classID
                    )
        ORDER BY proc_id;

    RETURN 1;

END
ELSE
BEGIN
    DECLARE @staffid INT;    
    SELECT @staffid = Staff_ID FROM dbo.uds_staff WHERE staff_name = @username;

    INSERT INTO @rightTable
        SELECT DISTINCT A.act_id 
                FROM 
                    dbo.UDS_Assign_Rule a,
                    dbo.uds_staff_in_position b
                WHERE
                    a.position_id = b.position_id 
                    and a.team_id = 0
                    and a.based_on = 1 /* position */
                    and b.staff_id = @staffid;

    INSERT INTO @rightTable
        SELECT DISTINCT A.act_id 
                FROM 
                    dbo.UDS_Assign_Rule a ,
                    dbo.uds_staff_in_role b
                WHERE
                    a.role_id = b.role_id 
                    and a.team_id = 0
                    and a.based_on = 3 /* role */
                    and b.staff_id = @staffid;

    INSERT INTO @rightTable
        SELECT DISTINCT A.act_id 
                FROM 
                    dbo.UDS_Assign_Rule a 
                WHERE
                    a.team_id = 0
                    and a.based_on = 4 /* staff */
                    and a.staff_id = @staffid;

    SELECT p.*
        FROM
            dbo.uds_proc p,
            @rightTable r
        WHERE p.proc_id = r.act_id
        ORDER BY p.proc_id;

    RETURN 0;
END

END
GO